package com.css.bjdt_report.cell.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.ArrayUtils;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.css.bjdt_report.cell.dao.CellDataDao;
import com.css.bjdt_report.cell.entity.CellData;
import com.css.bjdt_report.cell.entity.VarParam;
import com.css.bjdt_report.util.CommonUtil;
import com.css.bjdt_report.util.ReportConstant;

@Service
public class CellDataService extends CustomSqlService {
	@Autowired
	private Dao dao;
	@Autowired
	private CellDataDao cellDataDao;

	/**
	 * 按年度id、党组织id获取相关指标数据
	 * @param annualId 年度id
	 * @param unitId 党组织id
	 * @return
	 */
	public List<CellData> getCellData(String annualId, String unitId, String templateId) {
		List<CellData> list = cellDataDao.selectCellData(annualId, unitId, templateId, null, "1");
		if (list == null || list.size() == 0) {
			list = cellDataDao.selectCellData(annualId, unitId, templateId, null, null);
		}
		return list;
	}

	/**
	 * 按年度id、党组织id、单元格id获取相关指标数据
	 * @param annualId 年度id
	 * @param unitId 党组织id
	 * @param cellIds 单元格id
	 * @return 返回指标值，如果指标没有值，则不返回
	 */
	public List<CellData> getCellDataByCellIds(String annualId, String unitId, String[] cellIds) {
		/*		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
				StringBuilder sb = new StringBuilder();
				for (String cellId : cellIds) {
					sb.append("\'").append(cellId).append("\'").append(",");
				}
				String sqlStr = ReportConstant.EXECUTIVE_QUERY_CELL_SQL.replace("@cellIds",
						sb.toString().substring(0, sb.length() - 1));
				return executeSql(sqlStr, new MySqlCallback(), new VarParam("table_name", tableName, "dzz_id", unitId))
						.getList(CellData.class);*/
		List<CellData> list = cellDataDao.selectCellData(annualId, unitId, null, cellIds, "1");
		if (list == null || list.size() == 0) {
			list = cellDataDao.selectCellData(annualId, unitId, null, cellIds, null);
		}
		return list;
	}
	
	/**
	 * 按年度id、党组织id、单元格id获取相关指标数据
	 * @param annualId 年度id
	 * @param unitId 党组织id
	 * @param cellIds 单元格id
	 * @return 返回指标值，如果指标没有值(没有记录)，则new一个默认值为0的CellData返回
	 */
	public List<CellData> getAllCellDataByCellIds(String annualId, String unitId, String[] cellIds) {
		List<CellData> list = getCellDataByCellIds(annualId, unitId, cellIds);
		String[] existIds = new String[list.size()];
		for (int i = 0; i < list.size(); i++) {
			existIds[i] = list.get(i).getCellId();
		}
		for (String cellId : cellIds) {
			if(!ArrayUtils.contains(existIds,cellId)){
				CellData cellData = new CellData();
				cellData.setCellId(cellId);
				//参加校核公式的指标，如果没有被初始化(在数据库中没有记录)，那么其默认值为0
				cellData.setCellValue("0");
				list.add(cellData);
			}
		}
		return list;
	}

	public int countCellDataByTemplate(String annualId, String unitId, String cellId) {
		return cellDataDao.countCellDataByTemplate(annualId, unitId, cellId);
	}

	/**
	 * 删除
	 * @param annualId 年度id
	 * @param dzzId 党组织id
	 * @param templateId 报表id
	 */
	public void deleteCellData(String annualId, String dzzId, String templateId) {
		cellDataDao.deleteCellDataByDzzIdAndTemplateId(annualId, dzzId, templateId);
	}

	/**
	 * 删除
	 * @param annualId 年度id
	 * @param dzzId 党组织id
	 * @param templateId 报表id
	 */
	public void deleteSumCellData(String annualId, String dzzId, String templateId) {
		/*		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
				executeSql(ReportConstant.EXECUTIVE_DELETE_SQL_BY_BATCHID, null,
						new VarParam("table_name", tableName, "batch_id", batchId));*/
		cellDataDao.deleteSumCellDataByDzzIdAndTemplateId(annualId, dzzId, templateId);
	}

	//根据党组织删除该党组织下所有的报表数据
	public void deleteAllCellDataByDzzId(String annualId, String dzzId) {
		cellDataDao.deleteCellDataByDzzId(annualId, dzzId);
	}

	/**
	 * 按单元格类型删除(只删除统计类型、计算类型、引用类型指标)
	 * @param annualId 年度id
	 * @param unitId 党组织id
	 */
	public void deleteCellDataByCellType(String annualId, String unitId) {
		/*		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
				executeSql(ReportConstant.EXECUTIVE_DELETE_SQL_BY_CELLTYPE, null,
						new VarParam("table_name", tableName, "dzz_id", unitId));*/
		cellDataDao.deleteCellDataByCellType(annualId, unitId);
	}

	/**
	 * 汇总
	 * @param annualId 年度id
	 * @param cellId 单元格id
	 * @param unitIds 党组织id
	 * @return
	 */
	public String sumCellData(String annualId, String cellId, String[] unitIds) {
		/*		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
				StringBuilder sb = new StringBuilder();
				for (String unitId : unitIds) {
					sb.append("\'").append(unitId).append("\'").append(",");
				}
				String sqlStr = ReportConstant.EXECUTIVE_SUM_CELL_SQL.replace("@dzzIds",
						sb.toString().substring(0, sb.length() - 1));
				List<VarParam> varparams = new ArrayList<VarParam>();
				VarParam tableNameVar = new VarParam("tableName", tableName, null, null);
				VarParam annualIdParam = new VarParam(null, null, "annualId", annualId);
				VarParam cellIdParam = new VarParam(null, null, "cellId", cellId);
				varparams.add(tableNameVar);
				varparams.add(annualIdParam);
				varparams.add(cellIdParam);
				return executeSql(sqlStr, Sqls.callback.str(), varparams.toArray(new VarParam[0])).getString();*/
		return cellDataDao.sumCellData(annualId, cellId, unitIds);
	}

	/**
	 * 批量插入
	 * @param annualId 年度id
	 * @param cellDatas 单元格数据集合
	 */
	public void batchInsertCellData(String annualId, List<CellData> cellDatas) {
		/*		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
				if (cellDatas != null && cellDatas.size() > 0) {
					for (CellData cellData : cellDatas) {
						List<VarParam> varparams = new ArrayList<VarParam>();
						VarParam cellDataIdParam = new VarParam(null, null, "CELL_DATA_ID", CommonUtil.getUUID());
						VarParam cellIdParam = new VarParam(null, null, "CELL_ID", cellData.getCellId());
						VarParam cellValueParam = new VarParam(null, null, "CELL_VALUE", cellData.getCellValue());
						VarParam templateDataIdParam = new VarParam(null, null, "BATCH_ID",
								cellData.getBatchId());
						VarParam absRowNumParam = new VarParam(null, null, "ABS_ROW_NUM", cellData.getAbsRowNum().toString());
						VarParam absColNumParam = new VarParam(null, null, "ABS_COL_NUM", cellData.getAbsColNum().toString());
						String relRowNum = cellData.getRelRowNum() == null ? null : cellData.getRelRowNum().toString();
						VarParam relRowNumParam = new VarParam(null, null, "REL_ROW_NUM", relRowNum);
						String relColNum = cellData.getRelColNum() == null ? null : cellData.getRelColNum().toString();
						VarParam relColNumParam = new VarParam(null, null, "REL_COL_NUM", relColNum);
						VarParam unitIdParam = new VarParam(null, null, "DZZ_ID", cellData.getDzzId());
						VarParam tableNameVar = new VarParam("table_name", tableName, null, null);
						varparams.add(cellDataIdParam);
						varparams.add(cellIdParam);
						varparams.add(cellValueParam);
						varparams.add(templateDataIdParam);
						varparams.add(absRowNumParam);
						varparams.add(absColNumParam);
						varparams.add(relRowNumParam);
						varparams.add(relColNumParam);
						varparams.add(unitIdParam);
						varparams.add(tableNameVar);
						executeSql(ReportConstant.EXECUTIVE_INSERT_SQL, null, varparams.toArray(new VarParam[0]));
					}
				}*/
		if (cellDatas != null && cellDatas.size() > 0) {
			for (CellData cellData : cellDatas) {
				cellData.setCellDataId(CommonUtil.getUUID());
				cellDataDao.insertCellData(annualId, cellData);
			}
		}
	}
	
	/**
	 * 动态创建指标数据表
	 * @param annualId
	 */
	public void createTable(String annualId) {
		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		String count = executeSql(ReportConstant.ISEXIST_TABLE_SQL, Sqls.callback.str(), new VarParam(null, null, "table_name", tableName)).getString();
		if ("0".equals(count)) {
			executeSql(ReportConstant.EXECUTIVE_CREATE_TABLE_SQL, null, new VarParam("table_name", tableName, null, null));
		}
	}

	class MySqlCallback implements SqlCallback {

		@Override
		public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
			List<CellData> list = new ArrayList<CellData>();
			while (rs.next()) {
				CellData cellData = new CellData();
				cellData.setCellId(rs.getString("CELL_ID"));
				cellData.setCellDataId(rs.getString("CELL_DATA_ID"));
				cellData.setCellValue(rs.getString("CELL_VALUE"));
				cellData.setBatchId(rs.getString("BATCH_ID"));
				cellData.setDzzId(rs.getString("DZZ_ID"));
				cellData.setAbsRowNum(Integer.parseInt(rs.getString("ABS_ROW_NUM")));
				cellData.setAbsColNum(Integer.parseInt(rs.getString("ABS_COL_NUM")));
				if (rs.getString("REL_ROW_NUM") != null)
					cellData.setRelRowNum(Integer.parseInt(rs.getString("REL_ROW_NUM")));
				if (rs.getString("REL_COL_NUM") != null)
					cellData.setRelColNum(Integer.parseInt(rs.getString("REL_COL_NUM")));
				list.add(cellData);
			}
			return list;
		}
	}
}
